﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.Configuration;
using System.Data.SqlClient;
using SHelper;
using DevExpress.XtraSplashScreen;
using DevExpress.XtraWaitForm;
using System.IO;

namespace InvoiceImport
{
    public partial class ReportForm4 : DevExpress.XtraEditors.XtraForm
    {
        public ReportForm4()
        {
            InitializeComponent();
        }

        string con = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        public string ServerText { get; set; }
        public MainFrm mf { get; set; }
        DataTable SRtable, CBtable, Table1;
        string sdate1 = "", edate1 = "", sdate2 = "", edate2 = "", ItemName1 = "", ItemName2 = "";
        //行号
        private void gv_CustomDrawRowIndicator(object sender, DevExpress.XtraGrid.Views.Grid.RowIndicatorCustomDrawEventArgs e)
        {
            if (e.Info.IsRowIndicator)
            {
                e.Info.DisplayText = (e.RowHandle + 1).ToString();
            }
        }
        //查询
        private void SelectData()
        {
            var wf = new SplashScreenManager(this, typeof(DemoWaitForm), true, true);//进程
            wf.ShowWaitForm();
            wf.SetWaitFormCaption("正在查询...");
            #region 查询条件赋值
            
            #endregion
            #region 查询语句
            Table1 = SqlHelper.ExecuteDataTable(string.Format(@"


SELECT t.Location,t.Mode,t.Head,t.[Head Remark],t.[Tax Rate],
                                                                CASE WHEN t.收入本币无税金额 = '0.00' THEN NULL ELSE t.收入本币无税金额 END AS 收入,
                                                                CASE WHEN t.上月预估 = '0.00' THEN NULL ELSE t.上月预估 END AS 上月预估,
                                                                CASE WHEN t.当月预估 = '0.00' THEN NULL ELSE t.当月预估 END AS 当月预估,
                                                                CASE WHEN t.仮計上含む売上高 = '0.00' THEN NULL ELSE t.仮計上含む売上高 END AS 含预估总收入,
                                                                CASE WHEN t.成本本币无税金额 = '0.00' THEN NULL ELSE t.成本本币无税金额 END AS 成本,   
                                                                CASE WHEN t.上月预估1 = '0.00' THEN NULL ELSE t.上月预估1 END AS 上月预估1,
                                                                CASE WHEN t.当月预估1 = '0.00' THEN NULL ELSE t.当月预估1 END AS 当月预估1,
                                                                CASE WHEN t.仮計上含む原価 = '0.00' THEN NULL ELSE t.仮計上含む原価 END AS 含暂估总成本,
																CASE WHEN t.利润= '0.00' THEN NULL ELSE t.利润 END AS 利润,
																CASE WHEN t.上月预估2= '0.00' THEN NULL ELSE t.上月预估2 END AS 上月预估2,
																CASE WHEN t.当月预估2= '0.00' THEN NULL ELSE t.当月预估2 END AS 当月预估2,
																CASE WHEN t.含暂估总利润= '0.00' THEN NULL ELSE t.含暂估总利润 END AS 含暂估总利润 FROM (
                                                                SELECT  Location,Mode,Head,[Head Remark],[Tax Rate],
                                                                        CONVERT(NVARCHAR(50),收入本币无税金额) AS 收入本币无税金额 ,
                                                                        CONVERT(NVARCHAR(50),上月预估) AS 上月预估,
                                                                        CONVERT(NVARCHAR(50),当月预估) AS 当月预估,
                                                                        CONVERT(NVARCHAR(50),仮計上含む売上高) AS 仮計上含む売上高,
                                                                        CONVERT(NVARCHAR(50),成本本币无税金额) AS 成本本币无税金额,
                                                                        CONVERT(NVARCHAR(50),上月预估1) AS 上月预估1,
                                                                        CONVERT(NVARCHAR(50),当月预估1) AS 当月预估1,
                                                                        CONVERT(NVARCHAR(50),仮計上含む原価) AS 仮計上含む原価,
                                                                        number ,
                                                                        numberx,CONVERT(NVARCHAR(50),(收入本币无税金额-成本本币无税金额)) AS 利润,CONVERT(NVARCHAR(50),(上月预估-上月预估1)) AS 上月预估2,CONVERT(NVARCHAR(50),(当月预估-当月预估1)) AS 当月预估2,
                                                                CONVERT(NVARCHAR(50),((收入本币无税金额-成本本币无税金额)+(上月预估-上月预估1)+(当月预估-当月预估1))) AS 含暂估总利润 FROM Woo_ResultDN('"+Sdate.Text+ "')   UNION   SELECT '','','','','','','','','','','','','',10 AS number,14 AS numberx ,'','','','' UNION   SELECT 'TOTAL','','','','','收入','上月预估','本月预估','含预估总收入','成本','上月预估','本月预估','含预估总成本',11 AS number,15 AS numberx ,'利润','上月预估','本月预估','含暂估总利润' UNION   SELECT 'TOTAL','','','','',CONVERT(NVARCHAR(50),SUM(收入本币无税金额)),CONVERT(NVARCHAR(50),SUM(上月预估)),CONVERT(NVARCHAR(50),SUM(当月预估)),CONVERT(NVARCHAR(50),SUM(仮計上含む売上高)),CONVERT(NVARCHAR(50),SUM(成本本币无税金额)),CONVERT(NVARCHAR(50),SUM(上月预估1)),CONVERT(NVARCHAR(50),SUM(当月预估1)),CONVERT(NVARCHAR(50),SUM(仮計上含む原価)),12 AS number,16 AS numberx,CONVERT(NVARCHAR(50),SUM(收入本币无税金额-成本本币无税金额)) AS 利益,CONVERT(NVARCHAR(50),SUM(上月预估-上月预估1)) AS 上月预估2,CONVERT(NVARCHAR(50),SUM(当月预估-当月预估1)) AS 当月预估2, CONVERT(NVARCHAR(50),SUM((收入本币无税金额-成本本币无税金额)+(上月预估-上月预估1)+(当月预估-当月预估1))) AS 仮計上含む利益 FROM Woo_ResultDN('" + Sdate.Text + "') WHERE [Tax Rate]='小计') t order by t.numberx,t.number "), con, new SqlParameter());
            #endregion
            #region 添加项目合计
            //string tempItemNmae="";
            //int r = Table1.Rows.Count;
            //for (int i = 0; i < r; i++)
            //{
            //    if (tempItemNmae != Table1.Rows[i]["项目名称"].ToString().Trim())
            //    {
            //        tempItemNmae = Table1.Rows[i]["项目名称"].ToString().Trim();
            //        DataRow dr = Table1.NewRow();
            //        dr["部门"] = Table1.Rows[i]["部门"].ToString().Trim();
            //        dr["项目名称"] = Table1.Rows[i]["项目名称"].ToString().Trim();
            //        dr["存货编码"] = "合计";
            //        dr["成本原币无税金额"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "成本原币无税金额");
            //        dr["成本原币价税合计"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "成本原币价税合计");
            //        dr["成本本币无税金额"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "成本本币无税金额");
            //        dr["成本本币价税合计"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "成本本币价税合计");
            //        dr["收入原币无税金额"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "收入原币无税金额");
            //        dr["收入原币价税合计"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "收入原币价税合计");
            //        dr["收入本币无税金额"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "收入本币无税金额");
            //        dr["收入本币价税合计"] = GetSum(Table1.Select(string.Format("项目名称='{0}'", tempItemNmae)), "收入本币价税合计");
            //        Table1.Rows.Add(dr);
            //    }
            //}
            #endregion
            #region 添加利润合计
            //Table1.Columns.Add("原币无税利润", Type.GetType("System.Decimal"));
            //Table1.Columns.Add("原币含税利润", Type.GetType("System.Decimal"));
            //Table1.Columns.Add("本币无税利润", Type.GetType("System.Decimal"));
            //Table1.Columns.Add("本币含税利润", Type.GetType("System.Decimal"));
            //for (int i = 0; i < Table1.Rows.Count; i++)
            //{
            //    Table1.Rows[i]["原币无税利润"] = Math.Round((double.Parse(Table1.Rows[i]["收入原币无税金额"].ToString()) - double.Parse(Table1.Rows[i]["成本原币无税金额"].ToString())), 2).ToString("0.00");
            //    Table1.Rows[i]["原币含税利润"] = Math.Round((double.Parse(Table1.Rows[i]["收入原币价税合计"].ToString()) - double.Parse(Table1.Rows[i]["成本原币价税合计"].ToString())), 2).ToString("0.00");
            //    Table1.Rows[i]["本币无税利润"] = Math.Round((double.Parse(Table1.Rows[i]["收入本币无税金额"].ToString()) - double.Parse(Table1.Rows[i]["成本本币无税金额"].ToString())), 2).ToString("0.00");
            //    Table1.Rows[i]["本币含税利润"] = Math.Round((double.Parse(Table1.Rows[i]["收入本币价税合计"].ToString()) - double.Parse(Table1.Rows[i]["成本本币价税合计"].ToString())), 2).ToString("0.00");
            //}
            #endregion
            #region 添加总合计
            //DataView dv = Table1.DefaultView;
            //dv.Sort = "项目名称 Asc";
            //DataTable Table2 = dv.ToTable();
            //if (r > 0)
            //{
            //    DataRow dr = Table2.NewRow();
            //    dr["存货编码"] = "总合计";
            //    dr["成本原币无税金额"] = GetSum(Table1.Select(""), "成本原币无税金额");
            //    dr["成本原币价税合计"] = GetSum(Table1.Select(""), "成本原币价税合计");
            //    dr["成本本币无税金额"] = GetSum(Table1.Select(""), "成本本币无税金额");
            //    dr["成本本币价税合计"] = GetSum(Table1.Select(""), "成本本币价税合计");
            //    dr["收入原币无税金额"] = GetSum(Table1.Select(""), "收入原币无税金额");
            //    dr["收入原币价税合计"] = GetSum(Table1.Select(""), "收入原币价税合计");
            //    dr["收入本币无税金额"] = GetSum(Table1.Select(""), "收入本币无税金额");
            //    dr["收入本币价税合计"] = GetSum(Table1.Select(""), "收入本币价税合计");
            //    dr["原币无税利润"] = GetSum(Table1.Select(""), "收入原币无税金额");
            //    dr["原币含税利润"] = GetSum(Table1.Select(""), "收入原币价税合计");
            //    dr["本币无税利润"] = GetSum(Table1.Select(""), "收入本币无税金额");
            //    dr["本币含税利润"] = GetSum(Table1.Select(""), "收入本币价税合计");
            //    Table2.Rows.Add(dr);
            //}
            #endregion
            gc.DataSource = Table1;
            if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
        }

        private void gv_CellMerge(object sender, DevExpress.XtraGrid.Views.Grid.CellMergeEventArgs e)
        {
            int row1 = e.RowHandle1;
            int row2 = e.RowHandle2;
            e.Handled = true;
            if (e.Column.FieldName == "Location" || e.Column.FieldName  == "Mode")
            {
                string value1 = gv.GetDataRow(row1)["Location"].ToString();
                string value2 = gv.GetDataRow(row2)["Location"].ToString();
                if (value1 == value2)
                {
                    e.Handled = false;
                }
            }
        }

        //获取合计
        public string GetSum(DataRow[] drs, string ColumnName)
        {
            double sum = 0;
            foreach (var temp in drs)
            {
                try
                {
                    sum += double.Parse(temp[ColumnName].ToString());
                }
                catch { sum += 0; }
            }
            return Math.Round(sum, 2).ToString("0.00");
        }
        //加载
        private void ReportForm_Load(object sender, EventArgs e)
        {
            DateTime tempDate = DateTime.Today.AddMonths(-1);
            Sdate.DateTime = DateTime.Parse(tempDate.Year.ToString("0000") + "-" + tempDate.Month.ToString("00") + "-01");
            //Edate.DateTime = DateTime.Parse(tempDate.Year.ToString("0000") + "-" + tempDate.Month.ToString("00") + "-" + DateTime.DaysInMonth(tempDate.Year, tempDate.Month).ToString("00"));
            con = con.Replace("UFDATA_999_2013", ServerText);
        }
        //查询按钮
        private void SelectBtn_Click(object sender, EventArgs e)
        {
            SelectData();
        }
        //回车查询
        private void ItemText_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                SelectData();
            }
        }
    

        //导出Excel
        private void ExPortBtn_Click(object sender, EventArgs e)
        {
            if (gv.RowCount < 1)
            {
                MessageBox.Show("没有需要导出的数据!", "提示");
                return;
            }
            SaveFileDialog dialog = new SaveFileDialog()
            {
                FileName = string.Format("收入成本报表{0}-{1}-{2}", DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day),
                Filter = "Execl 文件(*.xls)|*.xls|Execl 文件(*.xlsx)|*.xlsx"
            };
            FileInfo KGFileInfo = null;
            FileInfo KGFileInfo1 = null;
            if (dialog.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            else
            {
                KGFileInfo = new FileInfo(dialog.FileName);
                KGFileInfo1 = new FileInfo(dialog.FileName);
            }
            try
            {
                switch (KGFileInfo.Extension.ToLower())
                {
                    case ".xls":
                        gv.ExportToXls(dialog.FileName);
                        break;
                    case ".xlsx":
                        gv.ExportToXlsx(dialog.FileName);
                        break;
                    default:
                        MessageBox.Show(string.Format("尚未支持的格式{0}", KGFileInfo.Extension), "提示");
                        return;
                }
                XtraMessageBox.Show("导出成功!", "提示");
            }
            catch (Exception ex)
            {
                XtraMessageBox.Show("导出失败!原因:" + ex, "提示");
            }
        }

        private void AreaSetting_Click(object sender, EventArgs e)
        {
            AreaSettingForm asf = new AreaSettingForm();
            asf.ShowDialog();
        }

        private void ReportForm_FormClosed(object sender, FormClosedEventArgs e)
        {
            mf.Show();
        }
    }
}